package action.report;

import hirondelle.date4j.DateTime;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.Region;

import exception.Exceptions;

import service.report.RiskManagementService;
import util.Time;
import util.Tool;

public class RiskManagementReportAction {
	private String searchtime="";
	private String Year;
	private String Month;
	private RiskManagementService rmService;
	private Map<String,BigDecimal> managelist;
	private String fileName;
	
	public String execute() throws Exceptions, ParseException{
		if(searchtime.equals("")){
			searchtime=Time.Timestamp2DateString(Time.CurrentTimestamp());
		}
		DateTime dt=new DateTime(searchtime);
		Year=dt.getYear().toString();
		Month=dt.getMonth().toString();
		managelist=rmService.getRiskReportMap(searchtime);
		return "success";
	}
	public String reportExport(){
		return "report";
	}
	public InputStream getInputStream() throws Exceptions {
		if(searchtime.equals("")){
			searchtime=Time.Timestamp2DateString(Time.CurrentTimestamp());
		}
		DateTime dt=new DateTime(searchtime);
		Year=dt.getYear().toString();
		Month=dt.getMonth().toString();
		Map<String,BigDecimal> list=rmService.getRiskReportMap(searchtime);
		HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中  
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 
        HSSFSheet sheet = createSheet(workbook, style);
        addMapRow(sheet, 1, list, style);

		//下载文件名
        fileName=Tool.toChinese("小额贷款公司风险信息监测表.xls");
        
        //输出下载
		ByteArrayOutputStream baos = new ByteArrayOutputStream();  
		try {
			workbook.write(baos);
		} catch (IOException e) {
			e.printStackTrace();
		}
        byte[] ba = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(ba);
        return bais;
	}
	@SuppressWarnings("deprecation")
	public int addMapRow(HSSFSheet sheet, int rowNumber, Map<String, BigDecimal> list, HSSFCellStyle style){
		HSSFRow row = sheet.createRow(6);
        row.createCell((short)0).setCellValue("青岛市黄岛区瑞丰小额贷款有限公司");
        row.createCell((short)1).setCellValue(list.get("totalbalance").toString());
        row.createCell((short)2).setCellValue(list.get("totaldiffer").toString());
        row.createCell((short)3).setCellValue(list.get("personalbalance").toString());
        row.createCell((short)4).setCellValue(list.get("personaldiffer").toString());
        row.createCell((short)5).setCellValue(list.get("personalNbalance").toString());
        row.createCell((short)6).setCellValue(list.get("personalNdiffer").toString());
        row.createCell((short)7).setCellValue(list.get("companybalance").toString());
        row.createCell((short)8).setCellValue(list.get("companydiffer").toString());
        row.createCell((short)9).setCellValue(list.get("trustbalance").toString());
        row.createCell((short)10).setCellValue(list.get("trustdiffer").toString());
        row.createCell((short)11).setCellValue(list.get("ensurebalance").toString());
        row.createCell((short)12).setCellValue(list.get("ensurediffer").toString());
        row.createCell((short)13).setCellValue(list.get("pledgebalance").toString());
        row.createCell((short)14).setCellValue(list.get("pledgediffer").toString());
        //row.createCell((short)15).setCellValue("");
        //row.createCell((short)16).setCellValue("");
        row.createCell((short)17).setCellValue(list.get("totalmoney").toString());
        row.createCell((short)18).setCellValue(list.get("totalmoneydiffer").toString());
        //row.createCell((short)19).setCellValue("");
       // row.createCell((short)20).setCellValue("");
        //row.createCell((short)21).setCellValue("");
        //row.createCell((short)22).setCellValue("");
        //row.createCell((short)23).setCellValue("");
       // row.createCell((short)24).setCellValue("");
		return rowNumber;
	}
	
	@SuppressWarnings("deprecation")
	public HSSFSheet createSheet(HSSFWorkbook workbook, HSSFCellStyle style){
		HSSFSheet sheet = workbook.createSheet("小额贷款公司风险信息检测表("+Year+"."+Month+")");
		
        //合并单元格
		sheet.addMergedRegion(new Region(0, (short)0, 0, (short)24));
		sheet.addMergedRegion(new Region(1, (short)0, 1, (short)24));
		sheet.addMergedRegion(new Region(2, (short)0, 2, (short)24));
        sheet.addMergedRegion(new Region(3, (short)0, 5, (short)0));
        sheet.addMergedRegion(new Region(3, (short)1, 4, (short)2));
        sheet.addMergedRegion(new Region(3, (short)3, 3, (short)8));
        sheet.addMergedRegion(new Region(3, (short)9, 3, (short)16));
        sheet.addMergedRegion(new Region(3, (short)17, 4, (short)18));
        sheet.addMergedRegion(new Region(3, (short)19, 4, (short)22));
        sheet.addMergedRegion(new Region(3, (short)23, 4, (short)24));
        sheet.addMergedRegion(new Region(5, (short)1, 5, (short)1));
        sheet.addMergedRegion(new Region(5, (short)2, 5, (short)2));
        sheet.addMergedRegion(new Region(4, (short)3, 4, (short)6));
        sheet.addMergedRegion(new Region(4, (short)7, 4, (short)8));
        sheet.addMergedRegion(new Region(4, (short)9, 4, (short)10));
        sheet.addMergedRegion(new Region(4, (short)11, 4, (short)12));
        sheet.addMergedRegion(new Region(4, (short)13, 4, (short)14));
        sheet.addMergedRegion(new Region(4, (short)15, 4, (short)16));
        sheet.addMergedRegion(new Region(5, (short)17, 5, (short)17));
        sheet.addMergedRegion(new Region(5, (short)18, 5, (short)18));
        sheet.addMergedRegion(new Region(5, (short)19, 5, (short)19));
        sheet.addMergedRegion(new Region(5, (short)20, 5, (short)20));
        sheet.addMergedRegion(new Region(5, (short)21, 5, (short)21));
        sheet.addMergedRegion(new Region(5, (short)22, 5, (short)22));
        sheet.addMergedRegion(new Region(5, (short)23, 5, (short)23));
        sheet.addMergedRegion(new Region(5, (short)24, 5, (short)24));
           
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = null;
       // header = sheet.createRow(0);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString("小额贷款公司风险信息检测表("+Year+"."+Month+")"));
        cell.setCellStyle(style);
        
        HSSFCellStyle style1 = workbook.createCellStyle();
		style1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 左右居中  
		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中  
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 
		header = sheet.createRow(1);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString("单位：万元、%"));
        cell.setCellStyle(style1);
        
        header = sheet.createRow(2);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString("填报机构:青岛市黄岛区瑞丰小额贷款有限公司"));
        cell.setCellStyle(style2);
        
        header = sheet.createRow(3);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString("小额贷款公司名称"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)1);
        cell.setCellValue(new HSSFRichTextString("贷款余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)3);
        cell.setCellValue(new HSSFRichTextString("贷款余额按贷款对象分"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)9);
        cell.setCellValue(new HSSFRichTextString("余额按贷款方式分"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)17);
        cell.setCellValue(new HSSFRichTextString("当面累计贷款额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)19);
        cell.setCellValue(new HSSFRichTextString("不良贷款"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)23);
        cell.setCellValue(new HSSFRichTextString("当期利润总额"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(4);               
        cell = header.createCell((short)3);
        cell.setCellValue(new HSSFRichTextString("个人贷款"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)7);
        cell.setCellValue(new HSSFRichTextString("企业贷款"));
        cell.setCellStyle(style);
        cell = header.createCell((short)9);
        cell.setCellValue(new HSSFRichTextString("信用贷款"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)11);
        cell.setCellValue(new HSSFRichTextString("第三方担保贷款"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)13);
        cell.setCellValue(new HSSFRichTextString("抵押贷款"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)15);
        cell.setCellValue(new HSSFRichTextString("其他"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(5);
        cell = header.createCell((short)1);
        cell.setCellValue(new HSSFRichTextString("本期期末"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)2);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)3);
        cell.setCellValue(new HSSFRichTextString("余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)4);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)5);
        cell.setCellValue(new HSSFRichTextString("其中：农户贷款余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)6);
        cell.setCellValue(new HSSFRichTextString("农户贷款余额比年初"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)7);
        cell.setCellValue(new HSSFRichTextString("余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)8);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        cell = header.createCell((short)9);
        cell.setCellValue(new HSSFRichTextString("余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)10);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)11);
        cell.setCellValue(new HSSFRichTextString("余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)12);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)13);
        cell.setCellValue(new HSSFRichTextString("余额"));
        cell.setCellStyle(style);
        cell = header.createCell((short)14);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)15);
        cell.setCellValue(new HSSFRichTextString("余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)16);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        cell = header.createCell((short)17);
        cell.setCellValue(new HSSFRichTextString("本期"));
        cell.setCellStyle(style);
        cell = header.createCell((short)18);
        cell.setCellValue(new HSSFRichTextString("比同期"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)19);
        cell.setCellValue(new HSSFRichTextString("余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)20);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)21);
        cell.setCellValue(new HSSFRichTextString("不良率"));
        cell.setCellStyle(style);
        cell = header.createCell((short)22);
        cell.setCellValue(new HSSFRichTextString("比年初"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)23);
        cell.setCellValue(new HSSFRichTextString("本期"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)24);
        cell.setCellValue(new HSSFRichTextString("比同期"));
        cell.setCellStyle(style);
        return sheet;
	}
	
	public RiskManagementService getRmService() {
		return rmService;
	}

	public void setRmService(RiskManagementService rmService) {
		this.rmService = rmService;
	}

	public Map<String,BigDecimal> getManagelist() {
		return managelist;
	}

	public void setManagelist(Map<String,BigDecimal> managelist) {
		this.managelist = managelist;
	}

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}
	public String getSearchtime() {
		return searchtime;
	}
	public void setSearchtime(String searchtime) {
		this.searchtime = searchtime;
	}
	public String getYear() {
		return Year;
	}
	public void setYear(String year) {
		Year = year;
	}
	public String getMonth() {
		return Month;
	}
	public void setMonth(String month) {
		Month = month;
	}

}
